| MySQL索引结构分类及其优劣选择详解 | 您所在的位置：网站首页 › mysql 索引 结构 › MySQL索引结构分类及其优劣选择详解 |
MySQL索引结构分类及其优劣选择详解
|
什么是索引？
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据库系统中，除了存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。这样就在这些数据上实现了高级查找算法，这种数据结构就是索引。 为什么要有索引？简单的来讲，在我们查找数据时，如果数据是乱序且无规律的，此时我们要查找一个数据只能进行遍历，从而找出来所需求的数据。但是如果给对应的数据建立了索引，则可以依据建立好的索引快速的找到需求数据。 建立索引的优缺点 优点： 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。可以大大加快数据的检索速度，这是创建索引的主要目的。可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。在排序时可以针对索引进行排序，降低数据排序的成本，降低CPU的消耗。 缺点： 存储数据索引也是需要占用一定空间。索引大大提高了查询效率，但是却降低了对数据表增删改操作效率。需要对数据进行增删改，还需要对索引进行调整。 索引的结构MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，大致包含以下几种： 索引结构描述B+Tree索引最常见的索引类型，大部分引擎都支持B+树索引Hash索引底层书库结构是使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询R-Tree（空间索引）是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少Full-text（全文索引）是一种通过建立倒排索引，快速匹配文档的方式。 存储引擎支持的索引结构 索引InnoDBMyISAMMemoryB+Tree索引支持支持支持Hash索引不支持不支持支持R-Tree（空间索引）不支持支持不支持Full-text（全文索引）5.6版本以后支持支持不支持不特殊说明的情况下，都是说的B+树索引，最为重要。 索引结构的优劣选择我们知道最常使用的都是B+树索引，那么有没有其他数据结构的索引呢？事实上无论什么数据结构都是可以做索引的，但是其查询的性能是不同的，相对来说，B+树所能应用的场景更多，效率也相对来讲更高一些，那么我们接下来久来探究常见的数据结构充当索引的缺点。 二叉树顺序插入时会退化为链表，降低查询效率。可以使用红黑树进行改进。 红黑树是一种自平衡的二叉查找树。虽然成功解决了退化为链表的情况，但是由于其底层还是二叉树，在大量数据的填充下，树的高度会比较大，从而查询效率还是会很低。 B树又称为多路平衡查找树，即一个节点中可以存储多个数据，这些数据在节点中顺序排列，其树的度可以是任意值，一般树的度都大于2。当等于2时退化为二叉树，每个节点存储一个数据，最多有两个子节点。 图下是一张5阶B树的示意图，5阶即为树的度，节点的最大子节点个树，每个节点中最多存储4个数据。 N阶B树，树的度为N，最多有N个子节点，节点中最多有N-1个数据。
在B树的数据填充中，如果某个节点的数据个数等于阶数，那么就会向上分裂。 这里我们需要思考一下，阶数为奇数和偶数的两种情况。 奇数：这里先简单的说一下奇数阶数的B树，其阶数为奇数，则其每个节点中最大能存储的数据个树就是偶数，那么此时再填充一个数据就需要进行分裂，此时一共有奇数个数据，奇数个数据则就有一个最中间的数据，此时我们只需要将这个中间的数向上分裂，左右两边的数据分裂为左右两个子节点。这个是简单的一种情况。 偶数：前面我们说了奇数阶的B树的分裂情况，但是对于偶数阶的B树，其节点中最多存储奇数个数据，此时再填充一个数据就需要进行分裂，此时一共有偶数个数据，无法找出来一个最中间的数进行向上分裂，这时候我们需要选择最中间偏左或者偏右的数据进行向上分裂，那么就会有两个节点中左节点的数据个树与右节点不同的现象。 B+树B+树实际上是B树的一个变种，我们知道在B树中，每个节点中都会存储数据，在查询过程中我们需要根据特性在每个节点中进行查找，B+树对此进行了升级，我们只在叶子结点存储数据，在非叶子节点中只存储索引，只起到索引的作用，在所有的叶子结点中有一条方向一样的指针，将非叶子结点连接成了一个单向链表。
在MySQL中的B+树索引，将标准的B+树结构进行改进，将所有的叶子结点连接成了一个循环双向链表，大大提高了查询效率。
B+树的查询、插入、删除、修改等操作和B树的逻辑是相似的，这里不过多赘述。 HashHash索引，又称哈希索引。就是采用一定的哈希算法，将键值对换算成新的哈希值，映射到对应的槽位上，然后存储在哈希表中。 通过哈希算法进行计算，则不可避免的会产生哈希碰撞，即不同的键值对通过哈希算法的计算得出了相同的哈希值，这种现象就叫做哈希碰撞，又叫哈希冲突。解决哈希碰撞的两种常见方法是开放寻址法、再哈希法、拉链法等。 开放寻址法：产生哈希碰撞以后，则从此处一直往后便利寻找，直到解决冲突为止。 再哈希法：将计算出来的哈希值再进行哈希，以此类推直到不产生碰撞为止。 拉链法：将所有产生碰撞的值，都存储在一条链表上。 哈希索引的特点 哈希索引只能用于对等比较（==，in），不支持范围查找。无法利用索引进行排序。查询效率高，通常情况下只需要一次检索就可以了，效率通常要高于B+树索引。 为什么InnoDB引擎选择B+树索引？ 相对于二叉树，B+树有更少的层级，搜索效率较高。对于B树来说，无论是叶子结点还是非叶子结点，都会存储数据，这样导致一页中存储的键值对减少，指针跟着减少，要保存同样多的数据，只能增加树的高度，导致效率下降。哈希索引不支持范围查找和排序操作，应用场景太局限。 索引的分类索引主要分为4个类型：主键索引、唯一索引、常规索引、全文索引
在InnoDB存储引擎中，根据索引的存储形式，又分为：聚集索引、二级索引 聚集索引选取规则： 如果存在主键，则主键索引就是聚集索引如果不存在主键索引，则会根据第一个非空唯一约束建立聚集索引。如果没有主键并且没有合适的唯一索引，那么InnoDB则会自动创建一个rowid作为隐藏的聚集索引。
在查询某个关键字的时候，如果需要查询二级索引，那么在查询二级索引以后并不会直接获取到数据，仅仅只会查询到对应行的id，然后再通过该id查询聚集索引，从而获取到需要的数据。这个过程我们叫做回表查询。 注意： select * from user where id = 10; select * from user where name = "tom";在相同条件下，第一条SQL的执行速度是高于第二条的。 因为第二条需要进行回表查询，第一条直接就能查出来需要的数据。 索引的语法 查看索引 SHOW INDEX FROM table_name;查看table_name表中的所有索引 创建索引 CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name(index_col_name,...);该SQL用来创建索引，创建联合索引时只需要将联合的字段填入table_name()函数内。 [UNIQUE | FULLTEXT] 为可选项 选择UNIQUE：即为创建一条唯一索引 选择FULLTEXT：即为创建一条全文索引 都不选：即创建一条默认索引 主键索引在建表时通过设定的主键自动创建 删除索引 DROP INDEX index_name ON table_name;删除table_name表中名为index_name的索引 索引名称的命名规则idx_tableName_fildName idx+表名+字段名 |
【本文地址】